package matlap.database.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import matlap.database.ConnectionManager;
import matlap.database.dao.RegistrationDAO;
import matlap.shared.Student;
import matlap.shared.exceptions.MyException;

public class RegistrationJdbcDAO implements RegistrationDAO {
	Connection con = null;

	/**
	 * Turns back the number from the regnumber table.
	 */

	public List<String> getRegNames() {
		List<String> list = new ArrayList<String>();
		String userName;
		String query = "SELECT username FROM registered ORDER BY username";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);

			int i = 0;

			while (rs.next()) {

				userName = rs.getString("username");
				list.add(i, userName);
				i++;

			}

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;

	}

	/**
	 * Turns back the number from the regnumber table.
	 */

	private int selectNumber() {

		int n = 0;
		String query = "SELECT number from regnumber";
		try {
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			while (rs.next())
				n = rs.getInt("number");
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		return n;
	}

	private String createUsername(String lastName, String firstName) {
		String userName = "";
		int n = selectNumber();
		n++;

		if (n < 10)
			userName = Accent.unAccent(lastName.substring(0, 2)).toLowerCase()
					+ Accent.unAccent(firstName.substring(0, 2)).toLowerCase()
					+ Integer.toString(0) + Integer.toString(0)
					+ Integer.toString(0) + Integer.toString(n);
		else if (n < 100)
			userName = Accent.unAccent(lastName.substring(0, 2)).toLowerCase()
					+ Accent.unAccent(firstName.substring(0, 2)).toLowerCase()
					+ Integer.toString(0) + Integer.toString(0)
					+ Integer.toString(n);
		else if (n < 1000)
			userName = Accent.unAccent(lastName.substring(0, 2).toLowerCase())
					+ Accent.unAccent(firstName.substring(0, 2)).toLowerCase()
					+ Integer.toString(0) + Integer.toString(n);
		else
			userName = Accent.unAccent(lastName.substring(0, 2).toLowerCase())
					+ Accent.unAccent(firstName.substring(0, 2)).toLowerCase()
					+ Integer.toString(n);

		updateNumber(n);
		return userName;
	}

	/**
	 * Inserts new people into people table, registered table and students
	 * table, by adding role, last name, first name, address, email, telephone
	 * number, school name and class, and turns back the username.
	 */

	// betesz uj regisztraltakat, akik meg nem kuldtek be, a people tablaba, a
	// regisztered tablaba es a students tablaba
	public String insertPeople(int role, Student student) {

		String userName = "";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			
			if(role==3 || role == 2){
				String query = "SELECT username, last_name, first_name from people WHERE role="	+ role + " AND last_name ='"+student.getLastName()+"' AND first_name = '"+student.getFirstName()+"'";
				Statement st = con.createStatement();
				ResultSet rs = st.executeQuery(query);
				int i = 0;
				while (rs.next()){
					userName = rs.getString("username");
					i++;
				}
				System.out.println(i);
				if(i==0){
					userName = createUsername(student.getLastName(),
							student.getFirstName());
					
					String s = new String("INSERT INTO people VALUES (" + "'"
							+ userName + "'," + role + ",'" + student.getLastName()
							+ "','" + student.getFirstName() + "','"
							+ student.getAddress() + "')");
		
					st.executeUpdate(s);
				}
			}
			else{
				userName = createUsername(student.getLastName(),
						student.getFirstName());
				System.out.println(userName);
				
				String s = new String("INSERT INTO people VALUES (" + "'"
						+ userName + "'," + role + ",'" + student.getLastName()
						+ "','" + student.getFirstName() + "','"
						+ student.getAddress() + "')");
	
				Statement st = con.createStatement();
				st.executeUpdate(s);
			}
			
			Hash hash = new Hash();
			String password = hash.hashPassword(student.getPassword());

			insertRegistered(userName, password, student.getEmail(),
					student.getPhone(), student.getAddress());

			if (role == 1) {
				insertStudents(userName, student.getSchool(),
						student.getClassNr());
			}
			if (role == 2) {
				insertTeachers(userName, 0);
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return userName;
	}

	private void insertTeachers(String userName, int role) {
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();

			String s = new String("INSERT INTO teacher VALUES ('" + userName
					+ "' ," + role + ")");

			Statement st = con.createStatement();
			st.executeUpdate(s);

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

	}

	/**
	 * Updates the number in the regnumber table.
	 */

	private void updateNumber(int number) {

		try {
			Statement st = con.createStatement();
			String s = new String("UPDATE regnumber SET number='" + number
					+ "'WHERE number=" + (number - 1));
			st.executeUpdate(s);
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
	}

	/**
	 * Inserts people into the registered table by adding username, password,
	 * email, telephone number and address.
	 */

	private void insertRegistered(String userName, String password,
			String email, String tel, String address) {

		String s = new String("INSERT INTO registered VALUES (" + "'"
				+ userName + "','" + password + "','" + email + "','" + tel
				+ "','" + address + "')");

		try {
			Statement st = con.createStatement();
			st.executeUpdate(s);
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
	}

	/**
	 * Inserts students into students table by adding username, school name and
	 * class.
	 */

	public void insertStudents(String userName, String schoolName, int classNr) {

		int schoolID = selectSchoolID(schoolName);
		String s = new String("INSERT INTO students VALUES (" + "'" + userName
				+ "'," + schoolID + "," + classNr + ")");

		try {
			Statement st = con.createStatement();
			st.executeUpdate(s);
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
	}

	/**
	 * Turns back the schoolID from the school table by adding school name.
	 */

	public int selectSchoolID(String schoolName) {

		String query = "SELECT schoolID from school WHERE school_name='"
				+ schoolName + "'";
		int schoolID = 0;
		try {
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			while (rs.next())
				schoolID = rs.getInt("schoolID");

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		return schoolID;
	}

	/**
	 * Inserts people to registered table, who exists in the people table, by
	 * adding last name, first name, email, tel and address.
	 */

	public String insertIntoRegistered(Student student) {

		String userName = "";

		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			userName = selectUserName(student.getLastName(),
					student.getFirstName());
			Hash hash = new Hash();
			String password = hash.hashPassword("password");
			String s = new String("INSERT INTO registered VALUES (" + "'"
					+ userName + "','" + password + "','" + student.getEmail()
					+ "','" + student.getPhone() + "','" + student.getAddress()
					+ "')");

			st.executeUpdate(s);
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return userName;
	}

	/**
	 * Turns back the username from the people table by adding last name and
	 * firs name.
	 */

	private String selectUserName(String lastName, String firstName) {

		String userName = "";
		String query = "SELECT username from people where last_name='"
				+ lastName + "' AND first_name='" + firstName + "' ORDER BY last_name, first_name";
		try {
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			while (rs.next())
				userName = rs.getString("username");
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		return userName;
	}

	/**
	 * Inserts people into people table by adding role, last name, first name
	 * and address.
	 */

	@Override
	public void insertInPeople(int role, String lastName, String firstName,
			String address) throws MyException {

		String userName = "";

		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();

			userName = createUsername(lastName, firstName);

			String s = new String("INSERT INTO people VALUES (" + "'"
					+ userName + "'," + role + ",'" + lastName + "','"
					+ firstName + "','" + address + "')");

			Statement st = con.createStatement();
			st.executeUpdate(s);

			if (role == 2)
				insertTeachers(userName, 1);

		} catch (SQLException ex) {
			throw new MyException("Nem sikerült a felvezetés. Ellenőrizd az adatokat és probálkozz újra!");
		} catch (ClassNotFoundException e) {
			throw new MyException("Nem sikerült a felvezetés. Ellenőrizd az adatokat és probálkozz újra!");
		}

		finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
			}
		}

	}

	/**
	 * Inserts students into people table by adding
	 */

	@Override
	public void insertStudentsPeople(String lastName, String firstName,
			String address, String schoolName, int classNr) throws MyException{

		String userName = "";

		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();

			userName = createUsername(lastName, firstName);

			String s = new String("INSERT INTO people VALUES ('" + userName
					+ "','1','" + lastName + "','" + firstName + "','"
					+ address + "')");

			Statement st = con.createStatement();
			st.executeUpdate(s);

			insertStudents(userName, schoolName, classNr);

		} catch (SQLException ex) {
			throw new MyException("Nem sikerült a felvezetés. Ellenőrizd az adatokat és probálkozz újra!");
		} catch (ClassNotFoundException e) {
			throw new MyException("Nem sikerült a felvezetés. Ellenőrizd az adatokat és probálkozz újra!");
		}

		finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
			}
		}
	}

	@Override
	public void updatePassword(String username, String p) {
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();

			Hash hash = new Hash();
			String p2 = hash.hashPassword(p);

			String s = new String("UPDATE registered SET password='" + p2
					+ "' WHERE username='" + username + "'");
			Statement st = con.createStatement();
			st.executeUpdate(s);
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	@Override
	public void updateContact(String username, String email, String phone,
			String address) {
		String s = new String("UPDATE `registered` SET `Email_address`='"
				+ email + "',`phone_number`=" + phone + ", `address`='"
				+ address + "' WHERE username='" + username + "'");

		try {
			Statement st = con.createStatement();
			st.executeUpdate(s);
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}

	}

	@Override
	public void updatePeople(String username, String last_name,
			String first_name, String address) {
		String s = new String("UPDATE `people` SET `last_name`='" + last_name
				+ "', `first_name`='" + first_name + "', `address`='" + address
				+ "' WHERE username='" + username + "'");

		try {
			Statement st = con.createStatement();
			st.executeUpdate(s);
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
	}

}
